package com.tong.embeddedvr.Mapper;

import com.tong.embeddedvr.dto.HomeworkDTO;
import com.tong.embeddedvr.entity.CollectionEntity;
import com.tong.embeddedvr.entity.HomeworkEntity;
import com.tong.embeddedvr.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * TODO
 *
 * @author gcl
 * @version 1.0
 * @Description
 * @date 2020/12/27 14:10
 */
@Repository
@Mapper
public interface HomeworkMapper {

    /**
     * @Description 添加作业,creatTime只在第一次更新,submitTime每次提交和保存都更新
     * @Author gcl
     * @Date 2020/12/27 20:29
     * @param: homeworkEntity
     * @Return java.lang.Integer
     */
    @Select("INSERT INTO homework(user_id,collection_id,homework_content,homework_create_time,homework_submit_time,homework_file_address,homework_state,homework_is_delete) VALUES(#{homeworkEntity.userId},#{homeworkEntity.collectionId},#{homeworkEntity.homeworkContent},#{homeworkEntity.homeworkCreateTime},#{homeworkEntity.homeworkSubmitTime},#{homeworkEntity.homeworkFileAddress},#{homeworkEntity.homeworkState},0)")
    Integer homeworkAdd(@Param("homeworkEntity") HomeworkEntity homeworkEntity) throws Exception;

    /**
     * @Description 修改homework信息, 使用xml配置
     * @Author gcl
     * @Date 2020/12/27 16:32
     * @param: homeworkEntity
     * @Return java.lang.Integer
     */
    Integer updateHomework(@Param("homeworkEntity") HomeworkEntity homeworkEntity) throws Exception;

    /**
     * @Description 根据collectionId查询对应的作业提交
     * @Author gcl
     * @Date 2020/12/27 16:21
     * @param: collectionEntity
     * @Return java.util.List<com.tong.embeddedvr.entity.HomeworkEntity>
     */
    @Select("SELECT * FROM homework WHERE collection_id = #{collectionEntity.collectionId} AND homework_is_delete = 0")
    List<HomeworkDTO> selectHomeworkByCollectionId(@Param("collectionEntity") CollectionEntity collectionEntity) throws Exception;

    /**
     * @Description 根据userId查询对应的作业提交
     * @Author gcl
     * @Date 2020/12/27 16:23
     * @param: userEntity
     * @Return java.util.List<com.tong.embeddedvr.entity.HomeworkEntity>
     */
    @Select("SELECT * FROM homework WHERE user_id = #{userEntity.userId} AND homework_is_delete = 0")
    List<HomeworkDTO> selectHomeworkByUserId(@Param("userEntity") UserEntity userEntity) throws Exception;

    /**
     * @Description 根据对应homeworkId获得homework信息
     * @Author gcl
     * @Date 2020/12/27 16:29
     * @param: homeworkEntity
     * @Return com.tong.embeddedvr.entity.HomeworkEntity
     */
    @Select("SELECT * FROM homework WHERE homework_id = #{homeworkEntity.homeworkId} AND homework_is_delete = 0")
    HomeworkDTO selectHomeworkById(@Param("homeworkEntity") HomeworkEntity homeworkEntity) throws Exception;

    /**
     * @Description 根据collectionId查询homeworkList
     * @Author gcl
     * @Date 2021/1/1 18:18
     * @param: queryText
     * @param: start
     * @param: pageSize
     * @param: collectionEntity
     * @Return java.util.List<com.tong.embeddedvr.dto.HomeworkDTO>
     */
    @Select("SELECT * FROM homework LEFT JOIN `user` ON homework.user_id = `user`.user_id\n" +
            "WHERE homework_is_delete=0 AND collection_id=#{collectionEntity.collectionId} AND user_nickname LIKE CONCAT('%', #{queryText}, '%')\n" +
            "ORDER BY homework_id\n" +
            "LIMIT #{start},#{pageSize}")
    List<HomeworkDTO> selectTableByCollectionId(@Param("queryText") String queryText,@Param("start") Integer start,@Param("pageSize") Integer pageSize, @Param("collectionEntity") CollectionEntity collectionEntity) throws Exception;

    @Select("SELECT COUNT(*) FROM homework LEFT JOIN `user` ON homework.user_id = `user`.user_id\n" +
            "WHERE homework_is_delete=0 AND collection_id=#{collectionEntity.collectionId} AND user_nickname LIKE CONCAT('%', #{queryText}, '%')")
    Integer selectCountByCollectionId(@Param("queryText") String queryText,@Param("collectionEntity") CollectionEntity collectionEntity) throws Exception;

    /**
     * @Description 根据学生的id获得对应的作业
     * @Author gcl
     * @Date 2021/1/2 15:52
     * @param: queryText
     * @param: start
     * @param: pageSize
     * @param: userEntity
     * @Return java.util.List<com.tong.embeddedvr.dto.HomeworkDTO>
     */
    @Select("SELECT * FROM homework LEFT JOIN collection ON homework.collection_id=collection.collection_id,`user`\n" +
            "WHERE homework.homework_is_delete=0 AND homework.user_id=`user`.user_id AND `user`.user_id = #{userEntity.userId} AND collection.collection_name LIKE CONCAT('%',#{queryText},'%')\n" +
            "ORDER BY homework.homework_id DESC\n" +
            "LIMIT #{start},#{pageSize}")
    List<HomeworkDTO> selectTableByUserId(@Param("queryText") String queryText,@Param("start") Integer start,@Param("pageSize") Integer pageSize,@Param("userEntity") UserEntity userEntity);

    /**
     * @Description 根据学生id获得总条目胡
     * @Author gcl
     * @Date 2021/1/2 15:52
     * @param: queryText
     * @param: userEntity
     * @Return java.lang.Integer
     */
    @Select("SELECT COUNT(*) FROM homework LEFT JOIN collection ON homework.collection_id=collection.collection_id,`user`\n" +
            "WHERE homework.homework_is_delete=0 AND homework.user_id=`user`.user_id AND homework.user_id=#{userEntity.userId} AND collection.collection_name LIKE CONCAT('%',#{queryText},'%')")
    Integer selectCountByUserId(@Param("queryText") String queryText,@Param("userEntity") UserEntity userEntity);

    /**
     * @Description 主要用来验证时会否有对应的作业
     * @Author gcl
     * @Date 2021/1/2 19:14
     * @param: userEntity
     * @param: collectionEntity
     * @Return java.lang.Integer
     */
    @Select("SELECT * FROM homework\n" +
            "WHERE homework_is_delete=0 AND collection_id = #{homeworkEntity.collectionId} AND user_id =#{homeworkEntity.userId}")
    HomeworkDTO selectHomeworkByUserIdAndCollectionId(@Param("homeworkEntity") HomeworkEntity homeworkEntity);
}
